package com.lahuan.base;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.lahuan.demo.entity.Order;
import com.lahuan.demo.service.OrderService;
import jdk.nashorn.internal.ir.TryNode;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.sql.DataSource;
import java.io.*;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Random;
import java.util.concurrent.atomic.AtomicInteger;


@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = MillionInsert.class)
@SpringBootApplication(scanBasePackages = "com.lahuan")
public class MillionInsert {

    @Autowired
    DataSource dataSource;
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    OrderService orderService;
    //待付款:wait 已付款:pay 已完成:finish 已取消:cancel 已无效:invalid
    private final String[] statusArray = {"wait", "pay", "finish", "cancel", "invalid"};

    /**
     * 时间戳 2020-01-01 00:00:00
     */
    long timeStart = 1577808000000L;
    /**
     * 时间戳 2021-12-31 23:59:59
     */
    long end = 1640966399000L;
    /**
     * 满多少条记录就提交
     */
    int commitCount = 10000;
    /**
     * 一天
     */
    long day = 1000 * 60 * 60 * 24;
    /**
     * 总数据大小
     */
    int total = 1_000;

    /**
     * 测试删改查  明确分库分表键
     */
    @Test
    public void testCrud1() {
        //随便执行一个sql，初始化connection
        orderService.getById(1);
        long start;
        long cost;
        //明确分库分表条件的删改查
        start = System.currentTimeMillis();
        orderService.remove(
                new LambdaQueryWrapper<Order>().eq(Order::getId, 1)
                        .eq(Order::getUserId, 1)
        );
        cost = System.currentTimeMillis() - start;
        log.info("明确分库分表条件的删除,耗时:{}ms", cost);
        start = System.currentTimeMillis();
        orderService.update(
                new LambdaUpdateWrapper<Order>().eq(Order::getId, 2)
                        .eq(Order::getUserId, 2)
                        .set(Order::getOrderSn, "xxxxxx")
        );
        cost = System.currentTimeMillis() - start;
        log.info("明确分库分表条件的修改,耗时:{}ms", cost);
        start = System.currentTimeMillis();
        orderService.list(
                new LambdaQueryWrapper<Order>().eq(Order::getId, 3)
                        .eq(Order::getUserId, 3)
        );
        cost = System.currentTimeMillis() - start;
        log.info("明确分库分表条件的查,耗时:{}ms", cost);
        Random rd = new Random();
        orderService.save(
                buildSingleData(rd.nextInt(Integer.MAX_VALUE) + 10_000_000)
        );
        cost = System.currentTimeMillis() - start;
        log.info("明确分库分表条件的新增,耗时:{}ms", cost);

    }

    /**
     * 测试删改查  不明确分库分表键
     */
    @Test
    public void testCrud2() {
        //随便执行一个sql，初始化connection
        orderService.getById(1);
        long start;
        long cost;
        start = System.currentTimeMillis();
        orderService.remove(
                new LambdaQueryWrapper<Order>().eq(Order::getTotalAmount, 99999)
        );
        cost = System.currentTimeMillis() - start;
        log.info("不明确分库分表条件的删除,耗时:{}ms", cost);
        start = System.currentTimeMillis();
        orderService.update(
                new LambdaUpdateWrapper<Order>().eq(Order::getOrderSn, "no_99999")
                        .set(Order::getOrderSn, "xxxxxx")
        );
        cost = System.currentTimeMillis() - start;
        log.info("不明确分库分表条件的修改,耗时:{}ms", cost);
        start = System.currentTimeMillis();
        orderService.list(
                new LambdaQueryWrapper<Order>().eq(Order::getOrderSn, "no_88888")
        );
        cost = System.currentTimeMillis() - start;
        log.info("不明确分库分表条件的查,耗时:{}ms", cost);

        try {
            Random rd = new Random();
            Order o = new Order();
            o.setOrderSn("test123");
            orderService.save(
                    o
            );
            cost = System.currentTimeMillis() - start;
            log.info("不明确分库分表条件的新增成功,耗时:{}ms", cost);
        } catch (Exception ex) {
            log.info("不明确分库分表条件的新增出现异常", ex);
        }
        try {
            start = System.currentTimeMillis();
            orderService.remove(
                    new LambdaQueryWrapper<Order>().gt(Order::getTotalAmount, 99999)
            );

            cost = System.currentTimeMillis() - start;
            log.info("对普通字段使用大于操作成功,耗时:{}ms", cost);
        } catch (Exception ex) {
            log.info("对普通字段使用大于操作成功出现异常", ex);
        }


        try {
            start = System.currentTimeMillis();
            orderService.remove(
                    new LambdaQueryWrapper<Order>().gt(Order::getId,2000)
            );

            cost = System.currentTimeMillis() - start;
            log.info(" 对分库分表key使用大于操作来删除出现成功,耗时:{}ms", cost);
        } catch (Exception ex) {
            log.info(" 对分库分表key使用大于操作来删除出现异常", ex);
        }




    }

    /**
     * 测试新增
     *
     * @throws Exception
     */
    @Test
    public void testManyThreadBatch() throws Exception {
        try {
            dropTable();
        } catch (Exception ex) {
            log.info("删除表异常,{}", ex.getMessage());
        }
        log.info("删除结束，开始创建表");
        creatTable();
        log.info("创建表结束");
        long startCreatObj = System.currentTimeMillis();
        Order[] data = buildDate();
        long costCreatObj = System.currentTimeMillis() - startCreatObj;
        log.info("创建对象完成,耗时:{}ms", costCreatObj);

        int cores = Runtime.getRuntime().availableProcessors();
        Thread[] threads = new Thread[cores];
        int perSize = total / cores;
        AtomicInteger left = new AtomicInteger(total);
        long startInsert = System.currentTimeMillis();
        for (int i = 0; i < cores; i++) {
            int start = i * perSize;
            if (i == cores - 1) {
                threads[i] = new Thread(() -> {
                    try {
                        runInsertBatch(start, left.get(), data);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
            } else {
                left.addAndGet(-perSize);
                threads[i] = new Thread(() -> {
                    try {
                        runInsertBatch(start, perSize, data);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
            }
            threads[i].setName("thread:" + i);
            threads[i].start();
        }
        for (int i = 0; i < cores; i++) {
            threads[i].join();
        }


        long costInsert = System.currentTimeMillis() - startInsert;

        log.info("多线程插入完成,核心数:{},数量:{},耗时:{}ms", cores, total, costInsert);
    }

    /**
     * 清空
     */
    @Test
    public void truncate() {
        jdbcTemplate.execute("truncate table t_order;");
    }

    /**
     * 删表
     */
    @Test
    public void dropTable() {
        jdbcTemplate.execute("drop table t_order");
    }

    /**
     * 建表
     */
    @Test
    public void creatTable() {
        jdbcTemplate.execute(
                "CREATE TABLE `t_order` (\n" +
                        "  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',\n" +
                        "  `user_id` bigint(20) NOT NULL COMMENT '用户 id',\n" +
                        "  `order_sn` varchar(32) DEFAULT NULL COMMENT '订单号',\n" +
                        "  `total_amount` decimal(20,2) NOT NULL COMMENT '订单总金额',\n" +
                        "  `order_status` varchar(16) NOT NULL COMMENT '状态  待付款:wait 已付款:pay 已完成:finish 已取消:cancel 已无效:invalid',\n" +
                        "  `is_pay` tinyint(2) NOT NULL COMMENT '是否已支付；0:未支付，1:支付成功 ',\n" +
                        "  `pay_time` datetime DEFAULT NULL COMMENT '支付完成时间',\n" +
                        "  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
                        "  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',\n" +
                        "  PRIMARY KEY (`id`) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单';");
    }

    private Order buildSingleData(int i) {
        Random rd = new Random();
        Order order = new Order();
        //主键是分库条件，所以需要再这里指定主键
        order.setId((long) i);
        order.setOrderSn("no_" + i);
        //金额 1-999
        order.setTotalAmount(new BigDecimal(rd.nextInt(999) + 1));
        //随机状态
        order.setOrderStatus(statusArray[rd.nextInt(statusArray.length)]);
        order.setIsPay(rd.nextInt(2));
        //2020-01-01 为起点，1年内的随机一天
        order.setPayTime(new Date(timeStart + (rd.nextInt(365) * day)));
        order.setUserId((long) (rd.nextInt(1024)));
        return order;
    }


    private Order[] buildDate() {
        Order[] result = new Order[total];
        for (int i = 0; i < total; i++) {
            result[i] = buildSingleData(i);
        }
        return result;
    }

    private void runInsertBatch(int start, int len, Order[] data) throws Exception {
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        //主键是分库条件，所以需要再这里指定主键
        //"INSERT INTO `t_order` ( `user_id`, `order_sn`, `total_amount`, `order_status`, `is_pay`, `pay_time`)VALUES\t( ?, ?, ?, ?, ?, ? );" ;
        String insert = "INSERT INTO `t_order` (`id`, `user_id`, `order_sn`, `total_amount`, `order_status`, `is_pay`, `pay_time`)VALUES\t( ?,?, ?, ?, ?, ?, ? )";
        PreparedStatement preparedStatement = connection.prepareStatement(insert);
        int count = 0;
        int total = 0;
        int maxRun = start + len;
        for (int i = start; i < maxRun; i++) {

            Order o = data[i];
            preparedStatement.setLong(1, o.getId());
            preparedStatement.setLong(2, o.getUserId());
            preparedStatement.setString(3, o.getOrderSn());
            preparedStatement.setBigDecimal(4, o.getTotalAmount());
            preparedStatement.setString(5, o.getOrderStatus());
            preparedStatement.setInt(6, o.getIsPay());
            preparedStatement.setDate(7, o.getPayTime());
            preparedStatement.addBatch();
            count++;
            total++;
            if (count >= commitCount) {
                preparedStatement.executeBatch();
                connection.commit();
                count = 0;
                log.info("{}:插入了:{}条", Thread.currentThread().getName(), total);
            }
        }
        if (count > 0) {
            preparedStatement.executeBatch();
            connection.commit();
            log.info("{}:循环结束,插入了:{}条", Thread.currentThread().getName(), total);
        }
        connection.close();
    }
}
